﻿from flask import Flask, g, render_template, request,flash,redirect,url_for,Blueprint
import pymysql,json,datetime,json
# from .login.login import *
from .setting import *

from flask_cors import CORS
article_blue = Blueprint('article',__name__)


def json_encoder(obj):
    if isinstance(obj, datetime.datetime):
        return obj.strftime("%Y-%m-%d %X")
    elif isinstance(obj, datetime.date):
        return obj.strftime("%Y-%m-%d")
@article_blue.route('/blog/article/list')
# @login_required
def articlelist():
	if request.args.get('title'):like = request.args.get('title')
	else:like=''
	if request.args.get('page'):page = request.args.get('page')
	else:page=1
	if request.args.get('limit'):limit = request.args.get('limit')
	else:limit=10
	# print(like,page,limit)
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql = "SELECT * from blog_article WHERE  title LIKE '%"+like+"%'  LIMIT "+str(limit) +" OFFSET " +str((int(page)-1)*int(limit))
	sqlcount = "SELECT count(*) from blog_article  WHERE  title LIKE '%"+like+"%'"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		dataList1 = cursor.fetchall()
		# for row in dataList:
			# print(row)
		dataList=[]
		for i in range(len(dataList1)):
			dataList.append({'id':dataList1[i][0],'title':dataList1[i][1],'author':dataList1[i][2],'visited':dataList1[i][3],'img':dataList1[i][4],'abstract':dataList1[i][5],'content':dataList1[i][6],'category':dataList1[i][7],'tags':dataList1[i][8],'createdAt':dataList1[i][9],'modifiedAt':dataList1[i][10]})
		cursor.execute(sqlcount)
		count = cursor.fetchall()[0][0]
	except Exception as e:
		return "Error: unable to fetch data from blog_article"+str(e)
	db.close()
	data={
			'msg': 'success',
			'code': 0,
			'page': {
				'totalCount': len(dataList),
				'pageSize': 10,
				'totalPage': 1,
				'currPage': 1,
				'list': dataList
				}
			}
	return json.dumps(data,default=json_encoder)
@article_blue.route('/blog/article/save',methods=['POST'])
# @login_required
def articlesave():
	if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))
		if 'title' in d.keys():title = d['title']
		else:title=''
		if 'author' in d.keys():author = d['author']
		else:author=''
		if 'visited' in d.keys():visited = d['visited']
		else:visited=''
		if 'img' in d.keys():img = d['img']
		else:img=''
		if 'abstract' in d.keys():abstract = d['abstract']
		else:abstract=''
		if 'content' in d.keys():content = d['content']
		else:content=''
		if 'category' in d.keys():category = d['category']
		else:category=''
		if 'tags' in d.keys():tags = d['tags']
		else:tags=''
		if 'createdAt' in d.keys():createdAt = d['createdAt']
		else:createdAt=''
		if 'modifiedAt' in d.keys():modifiedAt = d['modifiedAt']
		else:modifiedAt=''
	elif request.form:
		title = request.form['title']
		author = request.form['author']
		visited = request.form['visited']
		img = request.form['img']
		abstract = request.form['abstract']
		content = request.form['content']
		category = request.form['category']
		tags = request.form['tags']
		createdAt = request.form['createdAt']
		modifiedAt = request.form['modifiedAt']

	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql = 'insert into blog_article (title,author,visited,img,abstract,content,category,tags,created_at,modified_at) values('+"'"+str(title)+"',"+"'"+str(author)+"',"+"'"+str(visited)+"',"+"'"+str(img)+"',"+"'"+str(abstract)+"',"+"'"+str(content)+"',"+"'"+str(category)+"',"+"'"+str(tags)+"',"+"'"+str(createdAt)+"',"+"'"+str(modifiedAt)+"')"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		db.commit()
	except Exception as e:
		db.rollback()
		return "Error: unable to save data from blog_article"+str(e)
	db.close()
	
	data={
      'msg': 'success',
      'code': 0
    }
	return json.dumps(data,default=json_encoder)
@article_blue.route('/blog/article/update',methods=['POST'])
# @login_required
def articleupdate():
	if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))
		if 'id' in d.keys():id = d['id']
		else:id=''
		if 'title' in d.keys():title = d['title']
		else:title=''
		if 'author' in d.keys():author = d['author']
		else:author=''
		if 'visited' in d.keys():visited = d['visited']
		else:visited=''
		if 'img' in d.keys():img = d['img']
		else:img=''
		if 'abstract' in d.keys():abstract = d['abstract']
		else:abstract=''
		if 'content' in d.keys():content = d['content']
		else:content=''
		if 'category' in d.keys():category = d['category']
		else:category=''
		if 'tags' in d.keys():tags = d['tags']
		else:tags=''
		if 'createdAt' in d.keys():createdAt = d['createdAt']
		else:createdAt=''
		if 'modifiedAt' in d.keys():modifiedAt = d['modifiedAt']
		else:modifiedAt=''
	elif request.form:
		id = request.form['id']
		title = request.form['title']
		author = request.form['author']
		visited = request.form['visited']
		img = request.form['img']
		abstract = request.form['abstract']
		content = request.form['content']
		category = request.form['category']
		tags = request.form['tags']
		createdAt = request.form['createdAt']
		modifiedAt = request.form['modifiedAt']

	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	cursor = db.cursor()
	sql ="UPDATE blog_article SET title='"+str(title)+"',author='"+str(author)+"',visited='"+str(visited)+"',img='"+str(img)+"',abstract='"+str(abstract)+"',content='"+str(content)+"',category='"+str(category)+"',tags='"+str(tags)+"',created_at='"+str(createdAt)+"',modified_at='"+str(modifiedAt)+"'"+ "WHERE id = '"+str(id)+"'"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		db.commit()
	except Exception as e:
		db.rollback()
		return "Error: unable to delete data from blog_article"+str(e)
	db.close()

	data={
	  'msg': 'success',
	  'code': 0
	}
	return json.dumps(data,default=json_encoder)
@article_blue.route('/blog/article/delete',methods=['POST'])
# @login_required
def articledelete():
	# print("content_type:", request.headers.get("content_type"))
	# print("data:", request.data)
	# print("data:", str(request.data, 'UTF-8'))
	# print("form:", request.form)
	# print("files:", request.files)
	if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))
		if 'id' in d.keys():id = d['id']
		else:id=''
	elif request.form:
		id = request.form['id']

	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql ="DELETE FROM blog_article WHERE id='"+str(id)+"'"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		db.commit()
	except Exception as e:
		db.rollback()
		return "Error: unable to delete data from blog_article"+str(e)
	db.close()

	data={
	  'msg': 'success',
	  'code': 0
	}
	return json.dumps(data,default=json_encoder)
@article_blue.route('/blog/article/info')
@article_blue.route('/blog/article/info/<id>')#for update
# @login_required
def articleinfo(id=None):
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql ="SELECT * from blog_article where  id='"+str(id)+"'"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		dataList1 = cursor.fetchall()
		# for row in dataList:
			# print(row)
		article={'id':dataList1[0][0],'title':dataList1[0][1],'author':dataList1[0][2],'visited':dataList1[0][3],'img':dataList1[0][4],'abstract':dataList1[0][5],'content':dataList1[0][6],'category':dataList1[0][7],'tags':dataList1[0][8],'createdAt':dataList1[0][9],'modifiedAt':dataList1[0][10]}
		data={
				'msg': 'success',
				'code': 0,
				'article': article
				}
		return json.dumps(data,default=json_encoder)
	except Exception as e:
		return "Error: unable to fetch data from blog_article"+str(e)
	db.close()
@article_blue.route('/blog/article/select') #for other talbe's selector
# @login_required
def articleselect():
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql = "SELECT * from blog_article"
	if printsql:print(sql)
	try:
		cursor.execute(sql)
		dataList1 = cursor.fetchall()
		# for row in dataList1:
			# print(row)
		dataList=[]
		for i in range(len(dataList1)):
			dataList.append({'id':dataList1[i][0],'title':dataList1[i][1],'author':dataList1[i][2],'visited':dataList1[i][3],'img':dataList1[i][4],'abstract':dataList1[i][5],'content':dataList1[i][6],'category':dataList1[i][7],'tags':dataList1[i][8],'createdAt':dataList1[i][9],'modifiedAt':dataList1[i][10]})
		# print(dataList)
		data={
				'msg': 'success',
				'code': 0,
				'list': dataList
				}
		return json.dumps(data,default=json_encoder)
	except Exception as e:
		return "Error: unable to fetch data from sys_role"+str(e)
	db.close()	
	
	
	


